package com.serkol.modal.sql;

import com.serkol.utils.LangValidUtils;

public class SqlFeildModal {
    public static String getOtherTableColmuns(String tableName, String felid, String keyFelid) {
        String sql = "(SELECT " + felid + " FROM " + tableName + " where " + keyFelid + " LIMIT 1)";
        return sql;
    }

    public static String getPicture(String key) {
        String path =""; //AuthConstant.getPathUrl();
        String sql = "(SELECT IF(LOCATE('http',URL) > 0,URL,CONCAT('" + path + "',URL)) FROM cdnmanager. pictures WHERE ID=x." + key + "  LIMIT 1)";
        return sql;
    }
    public static String getAttrKeyValue(String attrKey, String value) {
        String sql = "(select NAME FROM cdnmanager.attribute_value where ATTRIBUTE_ID in (SELECT ID FROM cdnmanager.attribute where ISKEY ='" + attrKey + "') and IS_VALUE=" + value + ")";
        return sql;
    }

    public static String getUserAvatar(String key) {
        String sql = "(SELECT AVATAR FROM users where  ID=" + key + " LIMIT 1)";
        return sql;
    }
    public  static String getUserAddress(String key) {
        String sql="(SELECT concat_ws(' ',ADDRESS,ADDRESS_INFO) FROM  users_address where ID="+key+"   LIMIT 1)";
        return  sql;
    }
    public  static String getUserAddressPhoneNumber(String key) {
        String sql="(SELECT PHONE_NUMBER FROM  users_address where ID="+key+"   LIMIT 1)";
        return  sql;
    }

    public static String getUsersFeild() {

        StringBuilder sb = new StringBuilder();
        sb.append("ID as id,");

        sb.append("NICKNAME as nickname,");


        sb.append("AVATAR as avatar,");
        sb.append("USER_NAME as userName,");
        sb.append("PHONE as phone,");
        sb.append("CREATE_DATE as createDate,");
        // sb.append("(SELECT NAME FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS name,");
        //  sb.append("(SELECT BALANCE FROM users_wallet where user_id=x.ID LIMIT 1) AS balance,");
        //   sb.append("(SELECT SUM(PRICE) FROM users_wallet_log where user_id=x.ID AND STATE=1 LIMIT 1) AS monthPrice,");
        sb.append("(SELECT NICKNAME FROM users WHERE ID=x.PARENT_USER_ID LIMIT 1) AS parentName,");
        sb.append("(SELECT NAME FROM agent WHERE ID=x.AGENT_ID LIMIT 1) AS agentName");

        return sb.toString();

    }

    public static String getUsersFullFeild() {

        StringBuilder sb = new StringBuilder();
        sb.append("ID as id,");

        sb.append("NICKNAME as nickname,");

        sb.append("AVATAR as avatar,");
        sb.append("USER_NAME as userName,");
        sb.append("INVITE as invite,");
        sb.append("PHONE as phone,");
        sb.append("OPENID as openId,");
        sb.append("CREATE_DATE as createDate,");

        sb.append("AGENT_STATE as agentState,");
        sb.append("STATUS as status,");

        sb.append("(SELECT BALANCE FROM users_wallet where user_id=x.ID LIMIT 1) AS balance,");
        sb.append("(SELECT SUM(REQUEST_AMOUNT) FROM users_wallet_withdrawal WHERE USER_ID =x.ID  AND STATUS=1   LIMIT 1) AS withdrawal,");
        sb.append("(SELECT SUM(REQUEST_AMOUNT) FROM users_wallet_withdrawal WHERE USER_ID =x.ID AND STATUS=2  LIMIT 1) AS withdrawalWait,");
        sb.append("(SELECT COUNT(*) FROM users_wallet_withdrawal WHERE USER_ID =x.ID AND STATUS=1  LIMIT 1) AS withdrawaCount,");
        sb.append("(SELECT WECHAT FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS wechat,");
        sb.append("(SELECT IDCARD FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS idCard,");
        sb.append("(SELECT ADDRESS FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS address,");
        sb.append("(SELECT BANK_NAME FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS bankName,");
        sb.append("(SELECT BANK_ADDRESS FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS bankAddress,");
        sb.append("(SELECT BANK_CARD FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS bankCard,");
        sb.append("(SELECT NAME FROM users_authentication WHERE USER_ID=x.PARENT_USER_ID LIMIT 1) AS parentName,");
        sb.append("(SELECT PHONE FROM users_authentication WHERE USER_ID=x.PARENT_USER_ID LIMIT 1) AS parentPhone,");
        if (LangValidUtils.getValid("ug")) {
            sb.append("(SELECT NAME_UG FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS name,");
            sb.append("(SELECT NAME_UG FROM agent WHERE ID=x.AGENT_ID LIMIT 1) AS agentName");
        } else {
            sb.append("(SELECT NAME FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS name,");
            sb.append("(SELECT NAME FROM agent WHERE ID=x.AGENT_ID LIMIT 1) AS agentName");
        }

        return sb.toString();

    }


    public static String getUsersFullSourceFeild() {

        StringBuilder sb = new StringBuilder();
        sb.append("ID as id,");

        sb.append("NICKNAME as nickname,");

        sb.append("AVATAR as avatar,");
        sb.append("USER_NAME as userName,");
        sb.append("INVITE as invite,");
        sb.append("PHONE as phone,");
        sb.append("OPENID as openId,");
        sb.append("CREATE_DATE as createDate,");

        sb.append("AGENT_STATE as agentState,");
        sb.append("STATUS as status,");

        sb.append("(SELECT BALANCE FROM users_wallet where user_id=x.ID LIMIT 1) AS balance,");
        sb.append("(SELECT SUM(REQUEST_AMOUNT) FROM users_wallet_withdrawal WHERE USER_ID =x.ID  AND STATUS=1   LIMIT 1) AS withdrawal,");
        sb.append("(SELECT SUM(REQUEST_AMOUNT) FROM users_wallet_withdrawal WHERE USER_ID =x.ID AND STATUS=2  LIMIT 1) AS withdrawalWait,");
        sb.append("(SELECT COUNT(*) FROM users_wallet_withdrawal WHERE USER_ID =x.ID AND STATUS=1  LIMIT 1) AS withdrawaCount,");
        sb.append("(SELECT WECHAT FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS wechat,");
        sb.append("(SELECT IDCARD FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS idCard,");
        sb.append("(SELECT ADDRESS FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS address,");
        sb.append("(SELECT BANK_NAME FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS bankName,");
        sb.append("(SELECT BANK_ADDRESS FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS bankAddress,");
        sb.append("(SELECT BANK_CARD FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS bankCard,");
        sb.append("(SELECT NAME FROM users_authentication WHERE USER_ID=x.PARENT_USER_ID LIMIT 1) AS parentName,");
        sb.append("(SELECT PHONE FROM users_authentication WHERE USER_ID=x.PARENT_USER_ID LIMIT 1) AS parentPhone,");

        sb.append("(SELECT NAME FROM users_authentication WHERE USER_ID=x.ID LIMIT 1) AS name,");
        sb.append("(SELECT NAME FROM agent WHERE ID=x.AGENT_ID LIMIT 1) AS agentName");


        return sb.toString();

    }
    public static String getAuthName(String key) {
        String sql = "(SELECT NAME FROM users where  ID=" + key + " LIMIT 1)";
        return sql;
    }
    public static String getLoginLogStatastica() {
        String sql = "date_format(CREATE_DATE,'%Y-%m-%d') as ymd,date_format(CREATE_DATE,'%Y-%m') as ym,date_format(CREATE_DATE,'%d') as name,COUNT(*) as count ";
        return sql;
    }


    public static String getCurrentUser() {
        StringBuilder sb = new StringBuilder();
        sb.append("ID as id,");

        sb.append("NICKNAME as nickName,");
        sb.append("AVATAR as avatarUrl,");
        sb.append("AVATAR as avatar,");
        sb.append("USER_NAME as userName,");
        sb.append("USER_NAME as name,");
        sb.append("PHONE as phone,");
        sb.append("ROLE_ID as roleId,");
        sb.append("IS_ADMIN as isAdmin,");
        sb.append("CREATE_DATE as createDate");
      //  sb.append("(SELECT NICKNAME FROM users WHERE ID=x.PARENT_USER_ID LIMIT 1) AS parentName,");
      //  sb.append("(SELECT NAME FROM agent WHERE ID=x.AGENT_ID LIMIT 1) AS agentName");

        return sb.toString();
    }



}
